package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import bean.User;

import service.UserService;
import utils.DataSourceUtil;

public class UserDao {
	ResultSet rs = null;
	DataSourceUtil dsu = new DataSourceUtil();
//	 private static QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
	/**
	 * 得到总条数
	 */
	public List select() {
		String sql = "select * from userinfo";
		QueryRunner qr = new QueryRunner(dsu.getDataSource());
		try {
//			List<Map<String, Object>> list = qr.query( sql, new MapListHandler());
			List<User> list = qr.query(sql, new BeanListHandler(User.class));
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Connection getConnection() {
		Connection conn = null;
		String driver = "org.gjt.mm.mysql.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/usermanager";
		String username = "root";
		String password = "123456";
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			System.out.println("数据库连接出现问题，请及时处理!");
			e.printStackTrace();
		}
		return conn;
	}
	/**
	 * 数据删除
	 * 
	 * @param id
	 */
	public void remove(String username) {
		try {
			String sql = "delete from userinfo where username=?";
			PreparedStatement ps = getConnection().prepareStatement(sql);
			ps.setString(1, username);
			ps.executeUpdate();
		} catch (Exception e) {
			System.out.println("数据删除出现问题，请及时处理!");
			e.printStackTrace();
		}
	}
	/**
	 *  数据修改
	 * @param username
	 * @param password
	 * @param explains
	 * @param id
	 */
	public int update(String username, String password, String explains, String id) {
		try {
			String sql = "update userinfo set username=?,password=?,explains=? where id=?";
			PreparedStatement ps = getConnection().prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setString(3, explains);
			ps.setString(4, id);
			return ps.executeUpdate();
		} catch (Exception e) {
			System.out.println("数据修改出现问题，请及时处理!");
			e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 * 数据新增
	 * @param username
	 * @param password
	 * @param explains
	 */
	public int add(String username, String password, String explains) {
		try {
		String sql = "insert into userinfo(username, password, explains) value(?,?,?)";
		PreparedStatement ps = getConnection().prepareStatement(sql);
		ps.setString(1, username);
		ps.setString(2, password);
		ps.setString(3, explains);
		return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 关闭数据库连接
	 */
	public void close() {
		try {
			getConnection().close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		UserDao ud = new UserDao();
	
		ud.update("new", "67", "211", "12");
		UserService us = new UserService();
//		List<Map<String, Object>> list = us.getUserInfos();
		/*for (Map<String, Object> map : list) {
            int id = (Integer)map.get("id");
            System.out.println(id);
            String username = (String)map.get("username");
            int password = (Integer)map.get("password");
            String explains = (String)map.get("explains");
        }*/
		
		/*try {
			while (rs.next()) {
				System.out.println(rs.getString(1) + " " + rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}*/
		ud.close();
	}

}
